﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Services
{
   public class SQLCommand
   {


       #region Audit

       public const string GetAudit = @"select Audit.*, Users.UserName from Audit inner join Users on Audit.UserID = Users.ID where {0}";

       #endregion

       #region User

       public const string GetUserModuleRole = " select * from VwUserModuleRole where UserName ='{0}' and PassWord ='{1}'";
       public const string GetGroupModuleRole = " select * from VwGroupModuleRole where UserName ='{0}' and PassWord ='{1}'";
       public const string GetAllUserModuleRole = @"select User_Module_Role.*, Users.UserName, Module.name AS Module, Module.Description
                                                           from  User_Module_Role 
                                                           inner join Module on Module.ID = User_Module_Role.ModuleID
                                                           inner join Users on User_Module_Role.UserID = Users.ID where {0}";
       #endregion

       #region Group
       public const string GetAllGroupModuleRole = @"select Group_Module_Role.*, dbo.[Group].Name AS [Group], Module.name AS Module, Module.[Description]
                                                          from  Group_Module_Role inner join Module on Module.ID = Group_Module_Role.ModuleID
                                                            inner join [Group] on Group_Module_Role.GroupID = [Group].ID where {0}";
       #endregion

     


       #region Course

       public const string GetAllCourse = @"select Course.* from Course where {0} order by Year, Month DESC";

       public const string GetCourseByClassID = @"select Course.* from Course inner join Class on Class.CourseID = Course.ID
                                                     where {0}";

       #endregion


       #region Class
       public const string GetAllClass = @"select Class.*, Course.Name as CourseName, Branch.Name as BranchName,
                                                  Teacher.Name as TeacherName
                                                 
                                           from Class inner join Course on Class.CourseID = Course.ID
                                                      inner join Branch on Class.BranchID = Branch.ID
                                                      inner join Teacher on Class.MainTeacherID = Teacher.ID
                                           where {0}
                                           order by Class.StartDate DESC";


       public const string GetReportClass = @"select Class.ID, Class.Name,Class.StartDate,Class.EndDate,
                                                   Class.[Type],Class.Active, CASE WHEN Class.[Type] = 0 THEN N'Ngắn hạn' WHEN Class.[Type] =1 THEN N'Chuyên nghiệp'  END AS TypeString  ,
                                                    Class.Study_Time, Course.Name as CourseName, 
                                                    Branch1.Name as BranchName, Teacher.Name As TeacherName, Count(Student.ID) as Amount
                                                from Class inner join Course on Class.CourseID = Course.ID
                                                      inner join Student on Student.ClassID = Class.ID
                                                      inner join Branch on Class.BranchID = Branch.ID
                                                      inner join Branch Branch1 on Branch.ParentID = Branch1.ID
                                                      inner join Teacher on Class.MainTeacherID = Teacher.ID
                                               where {0}

                                               group by Branch1.Name,Class.[Type], Class.ID, Class.Name, Class.StartDate,Class.EndDate,
                                                        Course.Name, Teacher.Name, Class.Study_Time,Class.Active
                                               ";

       #endregion 



       #region Branch


       public const string GetBranchByClass = @"select Branch.* from Branch
                                                      
                                                       inner join Class on Class.BranchID = Branch.ID
                                                        where {0}";

       public const string GetAllBranch = @"Select Branch1.*, Branch2.Name as Parent from Branch Branch1 inner join 
                                                    Branch Branch2 on Branch1.ParentID = Branch2.ID where {0}";
       #endregion



       #region Student Fee


       public const string GetStudentFee = @"select Student_Fee.*,Student.Code as StudentCode, Student.Name as StudentName,Class.Name as ClassName,Teacher.Name as GVCN,
                                              Course.Name as CourseName, Branch.Name as BranchName,Student.TotalFee,
                                            Student.TypeOfFee as Type,Class.Type as ClassType, Student.Status as StudentStatus,Class.Description as ClassNote,Class.StartDate as ClassStartDate, Student.DateOfAdmission
                                             from Student_Fee left join Student on Student_Fee.StudentID =Student.ID
                                              inner join Class on Student.ClassID =Class.ID
                                              inner join Teacher on Class.MainTeacherID  =Teacher.ID
                                              inner join Course on Class.CourseID = Course.ID
                                              inner join Branch on Class.BranchID = Branch.ID where {0}";

       public const string GetStudentFeeForReport = @"select Class.Name as ClassName, Branch.Name AS BranchName,Course.Name as CourseName,
            Class.Description as ClassNote, Class.Type as ClassType ,sum(Student_Fee.Money) as TotalMoney,Student.Status as StudentStatus, Class.Size as ClassSize,
            CASE Student.Status WHEN 2 THEN count(Student.Status) ELSE '0' END AS SLBaoLUU,
            CASE Student.Status WHEN 4 THEN count(Student.Status) ELSE '0' END AS SLDangHoc,
            CASE Student.Status WHEN 9 THEN count(Student.Status) ELSE '0' END AS SLDATOTNGHIEP,
            CASE Student.Status WHEN 10 THEN count(Student.Status) ELSE '0' END AS SLNGHILUON
             from Student_Fee left join Student on Student_Fee.StudentID =Student.ID
              inner join Class on Student.ClassID =Class.ID
              inner join Teacher on Class.MainTeacherID  =Teacher.ID
              inner join Course on Class.CourseID = Course.ID
              inner join Branch on Class.BranchID = Branch.ID
              where {0}
              group by Class.Name,Branch.Name,Class.Description,Course.Name,Class.Type,Student.Status,Class.Size
              
                                            ";

       public const string GetStudentPayFeeForReport = @"select * from VwStudentInfo where {0} ";

       /// <summary>
       /// Lấy học sinh đóng phí báo lưu
       /// </summary>
       public const string GetStudentFeeSave = @"select Receipt_VoucherFee.Money ,Voucher.ReceiptNo,Voucher.CreateDate as ReceiptVoucherCreateDate from
                                             Receipt_VoucherFee inner join Voucher on Receipt_VoucherFee.VoucherID = Voucher.ID where {0}"; //dong phi bao luu

       #endregion



       #region Subject - Branch

       public const string GetSubjects = @"select Subject.*, Branch.Name as BranchName from Subject inner join Branch on Subject.BranchID = Branch.ID where {0}";

       #endregion

       #region Subject - Teacher

       public const string GetSubject_Teacher = @"select Subject_Teacher.*, Subject.Name as SubjectName,Teacher.Name as TeacherName
                                                     from Subject_Teacher inner join Subject on Subject_Teacher.SubjectID = Subject.ID
                                                      inner join Teacher on Subject_Teacher.TeacherID = Teacher.ID
                                                      where {0}";

       #endregion



       #region Subject - point

       public const string GetSubjectPointColumn = @" select Subject_PointColumn.*, 
                                                        [Subject].Name as SubjectName, 
                                                        PointColumn.Name as PointColumnName, PointColumn.Factor
                                                       from Subject_PointColumn, [Subject], PointColumn
                                                       where Subject_PointColumn.SubjectID = [Subject].ID 
                                                              and Subject_PointColumn.PointColumnID = PointColumn.ID
                                                              and {0}   ";

       #endregion



       #region Student - Result

       public const string GetStudent_ResultDetail = @"select * from VwStudentResult where {0}";

       public const string GetStudent_Result = @" select distinct  StudentID,
                                                 StudentCode, 
                                                    StudentName,
                                                    ClassName ,
                                                     CourseName, 
                                                      BranchName,
                                                     TeacherName from VwStudentResult where {0} ";

       #endregion

       #region Course_FeeTime

       public const string GetClassFeeTimeBYStudentANDType = @"select Class_FeeTime.* from 
          Student
          inner join Class on Student.ClassID = Class.ID
          --inner join Course on Class.CourseID = Course.ID 
          inner join Class_FeeTime on Class_FeeTime.ClassID = Class.ID
           where Student.ID ={0} and Class_FeeTime.[type] ={1}";


       public const string GetStudentFeeTimeByStudentIDANDType = @"select Student_FeeTime.* from
                          Student_FeeTime inner join Student on Student_FeeTime.StudentID = Student.ID
                           where Student_FeeTime.StudentID={0} and Student.TypeOfFee={1}";

       #endregion


       #region Student

       public const string GetAllStudent = @" select st.*,c.Name as Classname, t.Name as Teacher, c.Study_Time as Time,
                                                     co.Name as CourseName, br.Name as BranchName,br2.Name as Parent,
                                                     st_st.Name as statusname, ch.Name as channelName from Student st 
                                                                inner join Class c on st.ClassID = c.ID
                                                                left join Teacher t on t.ID = c.MainTeacherID
                                                                inner join Course co on c.CourseID = co.ID
                                                                inner join Branch br on c.BranchID = br.ID
                                                                inner join Branch br2 on br.ParentID = br2.ID
                                                                inner join Student_Status st_st on st.Status = st_st.ID 
                                                                inner join ChannelInfo ch on st.ChannelInfoID = ch.ID
                                                      where {0}
                                                ";
       public const string GetAllTempStudent = @"select TempStudent.*, ChannelInfo.Name as ChannelName, Student_Status.Name as StatusName
                                                 from TempStudent inner join dbo.ChannelInfo on TempStudent.ChannelInfoID = ChannelInfo.ID
                                                 inner join Student_Status on TempStudent.Status = Student_Status.ID
                                                 --inner join Branch on TempStudent.BranchID = Branch.ID
                                                  where {0}";


       public const string GetTempStudentDeposit =@"Select TempStudentDeposit.*, TempStudent.Name as TempStudentName, Branch.Name as BranchName
                                                   from TempStudentDeposit inner join TempStudent on TempStudentDeposit.TempStudentID = TempStudent.ID
                                                                           inner join Branch on TempStudentDeposit.BranchID = Branch.ID 
                                                   where {0}";


       /// <summary>
       /// Khi insert mới 1 học viên mặt định se lất tất cả môn học insert vào 
       /// bảng ket quả và mặt định điểm =0
       /// </summary>
       public const string GetAllSubjectPointColumnByClassID = @"select Subject_PointColumn.* 
                                        from Class inner join Course on Class.CourseID = Course.ID
                                         inner join Branch on Class.BranchID = Branch.ID
                                         inner join Subject on Subject.BranchID = Branch.ID
                                         inner join Subject_PointColumn on Subject_PointColumn.SubjectID = Subject.ID
                                         inner join PointColumn on Subject_PointColumn.PointColumnID =PointColumn.ID
                                         where {0}";


       #endregion


       #region Receipt fee Voucher

       public const string GetReceiptVoucher = @"select Voucher.*,Student.Name as StudentName,Class.Name as ClassName, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  ,TempStudent.Name as TempStudentName
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                            left join Receipt_VoucherFee on Receipt_VoucherFee.VoucherID = Voucher.ID
                                                                            left join Student on Receipt_VoucherFee.StudentID = Student.ID
                                                                             left join TempStudent on Receipt_VoucherFee.TempStudentID = TempStudent.ID 
                                                                            left join Class on Student.ClassID = Class.ID
                                                                            where {0}";


       /// <summary>
       /// Lay danh sach phieu thu hoc phi
       /// </summary>
       public const string GetReceiptFeeVoucher = @"select Receipt_VoucherFee.*, Student.Name as StudentName,Voucher.ReceiptNo
                                                                          from Receipt_VoucherFee 
                                                                       
                                                                           inner join Student on Receipt_VoucherFee.StudentID = Student.ID
                                                                           inner join Voucher on Receipt_VoucherFee.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion

       #region Receipt voucher apartment

       public const string GetReceiptvoucherapartment = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Receipt_VoucherApartment on Receipt_VoucherApartment.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion

       #region Receipt voucher Local

       public const string GetReceiptvoucherLocal = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Receipt_VoucherLocal on Receipt_VoucherLocal.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion

       #region Receipt voucher Salon

       public const string GetReceiptvoucherSalon = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Receipt_VoucherSalon on Receipt_VoucherSalon.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion


       #region Receipt voucher Sale

       public const string GetReceiptvoucherSale = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Receipt_VoucherSales on Receipt_VoucherSales.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion


       #region payment voucher apartment

       public const string GetPaymentvoucherApartment = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Payment_VoucherApartment on Payment_VoucherApartment.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion


       #region payment voucher school

       public const string GetPaymentvoucherSchool = @"select Voucher.*, Staff.Name as StaffName,Staff1.Name as StaffModifieName, 
                                                                           Payment_Method.Name as PaymentName,Payment_Reason.Name as PaymentReasonName  
                                                                           from Voucher 
                                                                           inner join Staff on Voucher.CreateBy = Staff.ID
                                                                           inner join Staff Staff1 on Voucher.ModifieBy = Staff1.ID
                                                                           inner join Payment_Method on Voucher.PaymentMethod = Payment_Method.ID
                                                                           inner join Payment_Reason on Voucher.ReceiptReason = Payment_Reason.ID 
                                                                           inner join Payment_VoucherSchool on Payment_VoucherSchool.VoucherID =Voucher.ID
                                                                            where {0}";

       #endregion


       #region Product

       public const string GetAllProducts = @"select Product.*,Product_Group.Name as ProductGroup,
                                             Product_Provider.Name as ProductProvider,stockunit.Name as StockUnitName,
                                             outunit.Name as OutUnitName,inunit.Name as InputUnitName  from Product 
                                             inner join Product_Group on Product.Product_GroupID = Product_Group.ID
                                             inner join Product_Provider on Product.Product_ProviderID = Product_Provider.ID
                                             inner join Product_Unit stockunit on Product.Stock_UnitID = stockunit.ID
                                             inner join Product_Unit outunit on Product.Output_UnitID = outunit.ID
                                             inner join Product_Unit inunit on Product.Input_UnitID = inunit.ID
                                             where {0}";

       public const string GetAllProductUnitChanges = @"select Product_UnitChange.*,unit.Name as Unit,unitchange.Name as Unitchange  from Product_UnitChange
                                                        inner join Product_Unit unit on Product_UnitChange.UnitID = unit.ID
                                                        inner join Product_Unit unitchange on Product_UnitChange.UnitChangeID =unitchange.ID
                                                        where {0}";

       #endregion

       #region StockVoucherDetail

       public const string GetStockVoucherDetail = @"select ";

       #endregion

   }
}
